/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package evaluate.report;

import evaluate.general.Beans;
import system.general.Logged;
import system.general.Message;
import system.general.Persistencia;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;
import javax.naming.NamingException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import system.general.Exc;

/**
 *
 * @author emojica
 */
@ManagedBean(name = "evaluacionReport")
@RequestScoped
public class Evaluates {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Message msg = new Message();
    private ResultSet rsts;
    private String sql;
    public String detailTable;
    public String headerTable;
    public String reportName;
    private List<Beans> observacionList = new ArrayList<>();
    private evaluate.general.Find evaluate_Find = new evaluate.general.Find();
    Map map = new LinkedHashMap();

    public Evaluates() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
        detailTable = "reportes.evaluacion_detalle_" + String.valueOf(logged.getLoggedId());
        headerTable = "reportes.evaluacion_general_header_" + String.valueOf(logged.getLoggedId());
        reportName = "evaluacion_programa.jasper";
    }

    public void report() throws Exc, SQLException, NamingException, Exception {
        try {
            Map params = new HashMap();
            system.general.Path path = new system.general.Path();

            //Elimina la tabla
            dropTable();

            //Crea la tabla 
            createTable();

            //inserta los datos
            loadData();

            //lee las propiedades y carga el mapa
            params.put("detailTable", detailTable);
            params.put("headerTable", headerTable);
            params.put("SUBREPORT_DIR", path.getEvaluateReportPath());
            params.put("logoPath", path.getLogoPathView());
            params.put("reportPath", path.getReportPathView());
            String jasperReport = path.getEvaluateReportPath() + reportName;
            this.persistencia.dbConnect();
            createReport(jasperReport, params, persistencia.getConn());

        } catch (JRException ex) {
            msg.showErr(namePackage(), "java", ex);
        } catch (IOException ex) {
        } catch (Exception ex) {
        } finally {
            this.persistencia.dbDisconnect();
        }
    }

    public Properties leerReportProperties() {
        Properties propiedades = new Properties();
        try {
            propiedades.load(getClass().getResourceAsStream("label_" + logged.getLoggedIdiom() + ".properties"));
            if (propiedades.isEmpty()) {
                return null;
            }
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
            return propiedades;
        }
    }

    public Properties leerProperties() {
        Properties propiedades = new Properties();
        try {
            propiedades.load(getClass().getResourceAsStream("/evaluate/general/label_es.properties"));
            if (propiedades.isEmpty()) {
                return null;
            }
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
            return propiedades;
        }
    }

    private void loadData() throws Exc, SQLException, NamingException, Exception {
        try {


            int preguntaNumero, numeroSolicitud;
            String descripcion_pregunta;
            String descripcion_respuesta;
            String universidad_nombre, carrera, recibido;
            String universidad_sede;
            Date fecha_supervision = new Date();
            String descripcion = "";
            boolean checked;
            String observacion_respuesta = "";

            sql = "SELECT sg.id as n_informe, pg.nombre || ' ' || pg.apellido AS recepcionista, ug.nombre AS universidad_nombre, "
                    + "us.nombre AS sede, sg.carrera, sg.recibido, sg.registrado  as fecha FROM solicitud_general sg "
                    + "LEFT JOIN solicitud_sede ss ON (sg.id = ss.id_solicitud) "
                    + "LEFT JOIN universidad_sede us ON (ss.id_sede = us.id) "
                    + "LEFT JOIN persona_general pg ON (pg.id = sg.id_recepcionista) "
                    + "LEFT JOIN universidad_facultad uf ON (sg.id_facultad = uf.id) "
                    + "LEFT JOIN universidad_general ug ON (ug.id = uf.id_universidad) "
                    + "WHERE sg.id =" + logged.getMasterId();

            rsts = persistencia.executeRsts(sql);
            rsts.next();
            universidad_nombre = rsts.getString("universidad_nombre");
            universidad_sede = rsts.getString("sede");
            numeroSolicitud = rsts.getInt("n_informe");
            fecha_supervision = rsts.getDate("fecha");
            carrera = rsts.getString("carrera");
            recibido = rsts.getString("recepcionista");



            rsts.close();

            Properties mp = leerReportProperties();
            sql = "INSERT INTO " + headerTable + " "
                    + "(etiqueta1, detalle1,"
                    + " etiqueta2, detalle2,"
                    + " etiqueta3, detalle3,"
                    + " etiqueta4, detalle4,"
                    + " etiqueta5, detalle5,"
                    + " etiqueta6, detalle6,"
                    + " etiqueta7, detalle7,"
                    + " etiqueta8, detalle8,"
                    + " etiqueta9, detalle9,"
                    + " etiqueta10, detalle10,"
                    + " etiqueta11, detalle11)"
                    + " VALUES ("
                    + "'" + mp.getProperty("field1") + "','" + numeroSolicitud + "',"
                    + "'" + mp.getProperty("field2") + "','" + universidad_nombre + "',"
                    + "'" + mp.getProperty("field6") + "','',"
                    + "'" + mp.getProperty("field4") + "','" + carrera + "',"
                    + "'" + mp.getProperty("field5") + "','" + recibido + "',"
                    + "'" + mp.getProperty("field10") + "','" + fecha_supervision + "',"
                    + "'" + mp.getProperty("field11") + "',' ',"
                    + "'" + mp.getProperty("field12") + "',' ',"
                    + "'" + mp.getProperty("field13") + "',' ',"
                    + "'" + mp.getProperty("field7") + "','',"
                    + "'" + mp.getProperty("title1") + "',' ')";
            persistencia.executeUpdate(sql);

            mp.clear();




            mp = leerProperties();
            for (Enumeration idl_pregunta = mp.keys(); idl_pregunta.hasMoreElements();) {
                Object obj = idl_pregunta.nextElement();
                String questionKey = obj.toString();
                if (questionKey.startsWith("pregunta")) {
                    String codigo = questionKey.substring(8);
                    Properties mp2 = leerProperties();
                    for (Enumeration descrip = mp2.keys(); descrip.hasMoreElements();) {
                        Object obj2 = descrip.nextElement();
                        String descripKey = obj2.toString();
                        if (descripKey.startsWith("descrip" + codigo)) {

                            descripcion = mp2.getProperty(descripKey);
                        }
                    }

                    preguntaNumero = Integer.parseInt(codigo);
                    descripcion_pregunta = mp.getProperty(questionKey);

                    map.clear();
                    map.put("id_solicitud = " + logged.getMasterId(), " AND ");
                    map.put("idl_pregunta = " + preguntaNumero, "  ");
                    checked = evaluate_Find.byExist(map);
                    descripcion_respuesta = mp.getProperty("respuesta0");
                    if (checked) {
                        observacion_respuesta = loadObs();
                        descripcion_respuesta = mp.getProperty("respuesta1");
                    }
                    sql = "INSERT  INTO " + detailTable + " "
                            + "(pregunta, respuesta, descripcion_pregunta,descripcion,descripcion_respuesta,observacion, checked) "
                            + "VALUES (" + preguntaNumero + ", 0,"
                            + "'" + descripcion_pregunta + "',"
                            + "'" + descripcion + "',"
                            + "'" + descripcion_respuesta + "',"
                            + "'" + observacion_respuesta + "',"
                            + checked + ")";
                    persistencia.executeUpdate(sql);
                }
            }

        } catch (Exc ex) {
            msg.showMsg(namePackage(), "system", ex.getIdError());
        } catch (SQLException ex) {
            msg.showErr(namePackage(), "sql", ex);
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
        }
    }

    private String loadObs() throws Exc, SQLException, NamingException, Exception {
        observacionList = evaluate_Find.byMap(map);
        return observacionList.get(0).getObservacion();

    }

    private void dropTable() throws Exc, SQLException, NamingException, Exception {
        sql = "DROP TABLE IF EXISTS " + headerTable + "," + detailTable;
        persistencia.executeUpdate(sql);
    }

    private void createTable() throws Exc, SQLException, NamingException, Exception {
        sql = "CREATE TABLE " + detailTable
                + " (pregunta integer, "
                + "respuesta integer,"
                + "descripcion_pregunta text,"
                + "descripcion text,"
                + "descripcion_respuesta text,"
                + "observacion text,"
                + "checked boolean) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + detailTable
                + " OWNER TO core;";

        sql += " CREATE TABLE " + headerTable
                + " (etiqueta1 text,"
                + "detalle1 text, "
                + "etiqueta2 text, "
                + "detalle2 text, "
                + "etiqueta3 text, "
                + "detalle3 text, "
                + "etiqueta4 text, "
                + "detalle4 text, "
                + "etiqueta5 text, "
                + "detalle5 text, "
                + "etiqueta6 text, "
                + "detalle6 text, "
                + "etiqueta7 text, "
                + "detalle7 text, "
                + "etiqueta8 text, "
                + "detalle8 text, "
                + "etiqueta9 text, "
                + "detalle9 text, "
                + "etiqueta10 text, "
                + "detalle10 text, "
                + "etiqueta11 text, "
                + "detalle11 text) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + headerTable
                + " OWNER TO core;";


        persistencia.executeUpdate(sql);
    }

    public boolean exist() {
        boolean exist = false;
        Map map = new LinkedHashMap();

        map.clear();

        map.put("id_solicitud = " + logged.getMasterId(), "");
        try {
            exist = evaluate_Find.byExist(map);

        } catch (Exception ex) {
        } finally {
        }
        return exist;
    }

    public void createReport(String archivoJasper, Map params, Connection ds) throws JRException, IOException {
        JasperPrint jasperPrint = JasperFillManager.fillReport(archivoJasper, params, ds);
        HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
        JasperExportManager.exportReportToPdfStream(jasperPrint, servletOutputStream);
    }

    private String namePackage() {
        Package pack = this.getClass().getPackage();
        return pack.getName();
    }
}
